package net.cyue.web.easyquery.provider.db.jdbc;

import net.cyue.util.StringUtil;
import net.cyue.web.easyquery.core.config.ConfigException;
import net.cyue.web.easyquery.core.db.api.ISQLExecutor;
import net.cyue.web.easyquery.core.db.util.SQLUtil;
import net.cyue.web.easyquery.core.provider.InstanceProviderLoader;
import net.cyue.web.easyquery.core.provider.api.IInstanceProvider;
import net.cyue.web.easyquery.provider.db.DBConfiguration;
import net.cyue.web.easyquery.provider.db.DBConfigurationUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class JDBCSQLExecutor implements ISQLExecutor {

    private static final String JDBC_PROPERTIES = "jdbc.properties";
    private final Logger logger = LoggerFactory.getLogger(this.toString());
    private DataSource dataSource;

    /**
     * 使用默认配置文件初始化
     * @throws IOException 配置文件读取异常
     * @throws ConfigException 配置异常
     */
    public JDBCSQLExecutor() throws IOException, ConfigException {
        this(DBConfigurationUtil.loadProperties(JDBC_PROPERTIES));
    }
    
    /**
     * 使用数据库配置初始化
     * @param configuration 数据库配置
     * @throws ConfigException 配置异常
     */
    public JDBCSQLExecutor(DBConfiguration configuration) throws ConfigException {
        this.setDataSource(createDataSource(configuration));
    }
    
    /**
     * 使用外部提供的DataSource初始化
     * @param dataSource 数据源
     */
    public JDBCSQLExecutor(DataSource dataSource) {
        this.setDataSource(dataSource);
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    /**
     * 创建 DataSource
     * @param configuration 数据库配置
     * @return DataSource
     * @throws ConfigException 配置异常
     */
    private DataSource createDataSource(DBConfiguration configuration) throws ConfigException {

        this.logger.info("数据库驱动：{}", configuration.getDriverName());
        if (!StringUtil.isBlank(configuration.getDatasource())) {
            this.logger.info("数据库数据源：{}", configuration.getDatasource());
        }

        ClassLoader cl = Thread.currentThread().getContextClassLoader();
        try {
            // 加载驱动
            Class.forName(configuration.getDriverName());
        } catch (ClassNotFoundException e) {
            throw new ConfigException(
                "\n" + e.getMessage() + "\n" +
                "数据库驱动加载失败: " + configuration.getDriverName() + "\n"
            );
        }

        try {
            if (!StringUtil.isBlank(configuration.getDatasource())) {
                Class<?> providerClass = cl.loadClass(configuration.getDatasource());
                return InstanceProviderLoader.create(
                    DataSource.class,
                    providerClass,
                    configuration
                ).getInstance();
            }
        } catch (ClassNotFoundException e) {
            this.logger.error(e.getMessage(), e);
            this.logger.error("未找到数据源提供者：{}", configuration.getDatasource());
        } catch (ConfigException e) {
            this.logger.error(e.getMessage(), e);
            this.logger.error("错误的数据源：{}", configuration.getDatasource());
        }

        try {
            IInstanceProvider<DataSource> provider = InstanceProviderLoader.load(
                DataSource.class,
                configuration
            );
            this.logger.info("使用数据源提供者：{}", provider.getClass().getName());
            return provider.getInstance();
        } catch (ConfigException | IOException e) {
            this.logger.warn(e.getMessage(), e);
            this.logger.warn("未找到数据源提供者，将使用默认数据源");
            return new SimpleDataSource(configuration);
        }
    }

    private static Map<String, Object> getResultMap(ResultSet rs) throws SQLException {
        Map<String, Object> map = new HashMap<>();
        ResultSetMetaData md = rs.getMetaData();
        int count = md.getColumnCount(); // 获取列的数量
        for (int i = 1; i <= count; i++) {
            String key = md.getColumnLabel(i);
            Object value = rs.getObject(i);
            map.put(key, value);
        }
        return map;
    }

    /**
     * 从DataSource获取连接
     * @return Connection
     * @throws SQLException SQL异常
     */
    protected Connection getConnection() throws SQLException {
        Connection conn = dataSource.getConnection();
        this.logger.debug("获取数据库连接: {}", conn);
        return conn;
    }
    
    @Override
    public List<Map<String, Object>> query(String sql, Map<String, Object> params) throws SQLException {
        String newSql = SQLUtil.fillParameters(sql, params);

        if (
            !params.isEmpty() &&
            (sql.contains("#{") || sql.contains("${")) &&
            newSql.equals(sql)
        ) {
            this.logger.warn("SQL参数未替换成功");
            return null;
        }
        this.logger.debug("\n收到 SQL：{}\n执行 SQL：{}", sql, newSql);
        // 每次操作从DataSource获取连接，并在完成后关闭
        List<Map<String, Object>> list = new ArrayList<>();
        try (
            Connection conn = getConnection();
            PreparedStatement sm = conn.prepareStatement(newSql)
        ) {
            ResultSet rs = sm.executeQuery();
            while(rs.next()) {
                list.add(getResultMap(rs));
            }
            rs.close();
            return list;
        }
    }

    @Override
    public int update(String sql, Map<String, Object> params) throws SQLException {
        String newSql = SQLUtil.fillParameters(sql, params);
        if (!params.isEmpty() && newSql.equals(sql)) {
            this.logger.warn("SQL参数未替换成功");
            return 0;
        }
        this.logger.debug("\n收到 SQL：{}\n执行 SQL：{}", sql, newSql);
        // 每次操作从DataSource获取连接，并在完成后关闭
        try (
            Connection conn = getConnection();
            PreparedStatement sm = conn.prepareStatement(newSql)
        ) {
            return sm.executeUpdate();
        }
    }
    
    /**
     * 实现批量更新操作
     * @param sql SQL语句
     * @param paramsList 参数列表
     * @return 影响的行数数组
     * @throws SQLException SQL异常
     */
    @Override
    public int[] batchUpdate(String sql, List<Map<String, Object>> paramsList) throws SQLException {
        if (paramsList == null || paramsList.isEmpty()) {
            return new int[0];
        }

        this.logger.debug("\n执行批量 SQL：{}", sql);
        // 每次批量操作从DataSource获取连接，并在完成后关闭
        try (
            Connection conn = getConnection();
            Statement sm = conn.createStatement()
        ) {
            // 禁用自动提交，提高批量操作性能
            conn.setAutoCommit(false);
            
            try {
                // 为每条参数添加到批处理
                for (Map<String, Object> params : paramsList) {
                    // 填充参数
                    String newSQL = SQLUtil.fillParameters(sql, params);
                    sm.addBatch(newSQL);
                }
                
                // 执行批处理
                int[] results = sm.executeBatch();
                
                // 提交事务
                conn.commit();
                
                this.logger.info("批量操作完成，共处理 {} 条记录", paramsList.size());
                return results;
            } catch (SQLException e) {
                // 发生异常时回滚事务
                conn.rollback();
                this.logger.error("批量操作失败：{}", e.getMessage(), e);
                throw e;
            } finally {
                // 恢复自动提交设置
                conn.setAutoCommit(true);
            }
        }
    }
}
